package com.webapp.model;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class CustomerRepModel {

	private Statement stmt = DatabaseConnector.connect();
	
	/*
	 * Takes the User object from the parameter, and adds him/her to the database, similar to User Registration.
	 */
	public boolean addCustomer(User user){
		try{
			if(!(stmt.executeQuery("SELECT * FROM users WHERE SSN = '" +user.getSSN()+ "'").next())){
				stmt.executeUpdate("INSERT INTO persons (SSN, Last_Name, First_Name, Address, City, State, Zip_Code, Telephone"+
						", Email_Address, password) VALUES ('" +user.getSSN()+ "', '" +user.getLastName()+ "', '" +user.getFirstName()+ "', '" +
						user.getAddress()+ "', '" +user.getCity()+ "', '" +user.getState()+ "', '" +user.getZip()+ "', '" +user.getPhone()+ 
						"', '" +user.getEmail()+ "', '" +user.getPassword()+ "')");
				stmt.executeUpdate("INSERT INTO users (SSN, User_Id, Account_Creation_Date, Rating) VALUES ('" +user.getSSN()+ 
						"', '" +user.getUserId()+ "', '" +user.getCreationDate()+ "', '" +user.getRating()+ "')");
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	/*
	 * Takes the User object from the parameter, and updates all values in the object to the correct row in the database
	 */
	public boolean editCustomer(User user){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE SSN = '" +user.getSSN()+ "'");
			if(rs.next()){
				int oldSocial = rs.getInt(1);
				
				stmt.executeUpdate("UPDATE persons SET SSN = '" +user.getSSN()+ "', Last_Name = '" +user.getLastName()+ "', First_Name = '" +user.getFirstName()+
						"', Address = '" +user.getAddress()+ "', City = '" +user.getCity()+ "', State = '" +user.getState()+ 
						"', Zip_Code = '" +user.getZip()+ "', Telephone = '" +user.getPhone()+ "', Email_Address = '" +user.getEmail()+
						"', password = '" +user.getPassword()+ "' WHERE SSN = '" +oldSocial+ "'");
				stmt.executeUpdate("UPDATE users SET SSN = '" +user.getSSN()+ "', User_Id = '" +user.getUserId()+ "', Account_Creation_Date = '" +user.getCreationDate()+
						"', Rating = '" +user.getRating()+ "' WHERE SSN = '" +oldSocial+ "'");
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	/*
	 * Takes the SSN provided and deletes that user from the database
	 */
	public boolean deleteCustomer(int social){
		try{
			if(stmt.executeQuery("SELECT * FROM users WHERE SSN = '" +social+ "'").next()){
				ResultSet rs = stmt.executeQuery("SELECT C.CircleID FROM circlemembership C "+
						"INNER JOIN users U ON C.CustomerID = U.User_Id WHERE U.SSN = '" +social+ "'");
				CircleModel m = new CircleModel();
				while(rs.next()){
					m.deleteCircle(rs.getInt(1));
				}
				
				rs = stmt.executeQuery("SELECT User_Id FROM users WHERE SSN = '" +social+ "'");
				rs.next();
				int userId = rs.getInt(1);
				stmt.executeUpdate("DELETE FROM user_preferences WHERE Id = '" +userId+ "'");
				
				rs = stmt.executeQuery("SELECT Account_Number FROM user_has_account WHERE User_Id = '" +userId+ "'");
				if(rs.next()){
					int accountNum = rs.getInt(1);
					stmt.executeUpdate("DELETE FROM user_has_account WHERE User_Id = '" +userId+ "'");
					stmt.executeUpdate("DELETE FROM accounts WHERE Account_Number = '" +accountNum+ "'");
				}
				
				stmt.executeUpdate("DELETE FROM users WHERE SSN = '" +social+ "'");
				stmt.executeUpdate("DELETE FROM persons WHERE SSN = '" +social+ "'");
				
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	/*
	 * Returns an array of Strings, each strings containing the email address of the user
	 */
	public ArrayList<String> mailingList(){
		try{
			ResultSet rs = stmt.executeQuery("SELECT P.Email_Address FROM persons P, users U WHERE P.SSN = U.SSN");
			ArrayList<String> list = new ArrayList<String>();
			
			while(rs.next()){
				list.add(rs.getString(1));
			}
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Returns an array of ITEM NAMES suggested for a user, based on past transactions
	 */
	public ArrayList<Advertisement> suggestItemList(int userId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM user_has_account WHERE User_Id = '" +userId+ "'");
			if(rs.next()){
				rs = stmt.executeQuery("SELECT A.* FROM advertisement A WHERE A.Type IN(SELECT A.Type FROM advertisement A"+
						", purchases P WHERE A.Advertisement_Id = P.Advertisement AND P.Account IN(SELECT H.Account_Number FROM user_has_account H"+
						", users U WHERE H.User_Id = U.User_Id AND U.User_Id = '" +userId+ "'))");
				
				ArrayList<Advertisement> list = new ArrayList<Advertisement>();
				while(rs.next()){
					Advertisement a = new Advertisement();
					a.setAdvertisementId(rs.getInt(1));
					a.setEmployeeId(rs.getInt(2));
					a.setType(rs.getString(3));
					a.setDate(rs.getString(4));
					a.setCompany(rs.getString(5));
					a.setItemName(rs.getString(6));
					a.setContent(rs.getString(7));
					a.setPrice(rs.getInt(8));
					a.setUnits(rs.getInt(9));
					
					list.add(a);
				}
				return list;
			}
			else
				return null;
		}catch(Exception ex){return null;}
	}
}
